package com.sjq.common.dao.impl;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Repository;

import com.sjq.common.dao.GenericDao;
import com.sjq.common.dao.RoleDao;
import com.sjq.common.entity.Role;
import com.sjq.common.util.PageResults;

@Repository("roleDao")
public class RoleDaoImpl extends GenericDao<Role> implements RoleDao {
	// 单个角色查询
	@Override
	public Map<String, Object> selectSingleRole(int js_id) {
		StringBuilder sql = new StringBuilder()
				.append(" select * from t_pz_sys_role where js_id= ? ");
		Object[] params = new Object[] { js_id };
		Map<String, Object> map = this.getJdbcTemplate().queryForMap(
				sql.toString(), params);
		return map;
	}

	// 分页查询
	@Override
	public PageResults<?> selectRoleByPage(int pageNo, int pageSize, Role role) {
		StringBuilder paramSql = new StringBuilder();
		List params = new ArrayList();
		if (role.getJSMC() != null && !(role.getJSMC().equals(""))) {
			paramSql.append(" and jsmc like ? ");
			params.add("%" + role.getJSMC().trim() + "%");
		}
		if (role.getJS_ID() != "-1" && role.getJS_ID() != null
				&& !(role.getJS_ID().equals(""))) {
			paramSql.append(" and js_id = ? ");
			params.add(role.getJS_ID().trim());
		}
		PageResults<Map<String, Object>> results = new PageResults<Map<String, Object>>();
		StringBuilder pageSql = new StringBuilder()
				// .append(" select * from t_pz_sys_role ")
				.append("select * from ")
				.append("(select tmp_1.*, rownum as rowindex from  (select t.* from t_pz_sys_role t where djbgbs<>'3' order by jssx) tmp_1  ) tmp ")
				.append(" where rowindex between ").append(
						((pageNo - 1) * pageSize + 1)).append(" and ").append(
						((pageNo - 1) * pageSize + pageSize)).append(
						paramSql.toString());
		//System.out.println("pageSql_role--" + pageSql);

		List<Map<String, Object>> list = this.getJdbcTemplate().queryForList(
				pageSql.toString(), params.toArray());
		results.setPageSize(pageSize);
		String sql = "select count(1) num from t_pz_sys_role where 1=1 and djbgbs<>'3' "
				+ paramSql.toString();
		SqlRowSet sqlRowSet = this.getJdbcTemplate().queryForRowSet(sql,
				params.toArray());
		int count = 0;
		while (sqlRowSet.next()) {
			count = sqlRowSet.getInt("num");
		}
		results.setTotalCount(count);
		results.setPageNo(pageNo);
		results.setResults(list);
		return results;
	}

	// 查询所有
	@Override
	public PageResults<?> selectAllRole() {
		StringBuilder paramSql = new StringBuilder();
		List params = new ArrayList();
		PageResults<Map<String, Object>> results = new PageResults<Map<String, Object>>();
		StringBuilder pageSql = new StringBuilder()
				.append(" select * from t_pz_sys_role where djbgbs<>'3' order by jssx ");
		List<Map<String, Object>> list = this.getJdbcTemplate().queryForList(
				pageSql.toString(), params.toArray());
		// results.setPageSize(pageSize);
		String sql = "select count(1) num from t_pz_sys_role where 1=1 and djbgbs<>'3' "
				+ paramSql.toString();
		SqlRowSet sqlRowSet = this.getJdbcTemplate().queryForRowSet(sql,
				params.toArray());
		int count = 0;
		while (sqlRowSet.next()) {
			count = sqlRowSet.getInt("num");
		}
		results.setTotalCount(count);
		// results.setPageNo(pageNo);
		results.setResults(list);
		return results;
	}

	// 插入
	@Override
	public void insertRole(Role role) {
		String maxbhSql = "select lpad(to_char(nvl(max(to_number(substr(JS_ID,-4))),0)+1),4,'0') maxbh from t_pz_sys_role";
		SqlRowSet srs = this.getJdbcTemplate().queryForRowSet(maxbhSql);
		String maxbh = "";
		if (srs.next()) {
			maxbh = srs.getString("maxbh");
		}
		StringBuilder sql = new StringBuilder().append(
				" insert into t_pz_sys_role ").append(
				" (js_id,jsmc,jssx,djr_yhm,djsj,djbgbs) ").append(
				" values(?,?,?,'admin',sysdate,'1') ");
		Object[] params = { maxbh, role.getJSMC(), role.getJSSX() };
		this.getJdbcTemplate().update(sql.toString(), params);
	}

	// 修改
	@Override
	public void updateRole(Role role) {
		StringBuilder sql = new StringBuilder().append(
				" update t_pz_sys_role set ").append(
				" jsmc=?,djbgsj=sysdate,djbgbs='2' ").append(" where js_id=? ");
		Object[] params = new Object[] { role.getJSMC(), role.getJS_ID() };
		this.getJdbcTemplate().update(sql.toString(), params);
	}

	// 删除
	@Override
	public void deleteRole(int js_id) {
		StringBuilder sql = new StringBuilder()
				.append(" delete from  t_pz_sys_role where js_id = ? ");
		this.getJdbcTemplate().update(sql.toString(), js_id);
	}

	@Override
	public int judge(int js_id) {
		String sql = " select count(1) is_used from t_wf_sys_user where t_wf_sys_user.user_id in (select user_id from t_pz_sys_role_user where js_id=?) ";
		SqlRowSet i = this.getJdbcTemplate().queryForRowSet(sql, js_id);
		i.next();
		return i.getInt("IS_USED");
	}

	@Override
	public List<Map<String, Object>> selectRole() {
		String sql = " select * from t_pz_sys_role ";
		List<Map<String, Object>> list = this.getJdbcTemplate().queryForList(
				sql);
		return list;
	}

}
